/*
 * SQLSearchVisitor.java Copyright 2004 Paul Mukherjee
 */
package org.nanhill.support.hibernate.search.demo.filesearch;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.nanhill.commons.pattern.search.AbstractSearchVisitor;
import org.nanhill.commons.pattern.search.SearchCriterion;
import org.nanhill.commons.pattern.search.SearchException;

/**
 * @author pmu
 * @version $Revision: 1.5 $
 * 
 * Implementation of ISearchVisitor that uses SQL and JDBC.
 */
public class SQLSearchVisitor extends AbstractSearchVisitor implements
		VolumeSearchVisitor {

	/* Names of the database tables. */
	private static final String FILE_TB = "FILE_TB";

	private static final String VOLUME_TB = "VOLUME_TB";

	private static final String KEYWORD_TB = "KEYWORD_TB";

	private static final String KEYWORD_VOLUME_REL = "KEYWORD_VOLUME_REL";

	/**
	 * The list of columns to be extracted from the database.
	 */
	private List<String> selects = new ArrayList<String>();

	/**
	 * The set of tables to be queried.
	 */
	private Set<String> tables = new HashSet<String>();

	/**
	 * The list of criteria to be used to constrain the search. This list may
	 * include parameter place holder characters that need to be populated.
	 */
	private List<String> criteria = new ArrayList<String>();

	/**
	 * The list of parameters for the search. Each parameter corresponds to a
	 * parameter place holder in the criteria list.
	 */
	private List<String> parameters = new ArrayList<String>();

	/**
	 * The connection to the database
	 */
	private Connection conn;

	/**
	 * Creates an instance of this class.
	 * 
	 * @param conn
	 *            The connection to the database.
	 */
	public SQLSearchVisitor(Connection conn) {
		this.conn = conn;
	}

	/**
	 * Builds up the SQL query by visiting each ISearchCriterion object in the
	 * provided list.
	 * 
	 * @param searchCriteria
	 *            List of ISearchCriterion objects to visit.
	 * @throws SearchException
	 *             If there was a problem building the query.
	 */
	protected void buildQuery(List<SearchCriterion> searchCriteria)
			throws SearchException {
		addFixedInformation();
		for (Iterator<SearchCriterion> itt = searchCriteria.iterator(); itt
				.hasNext();) {
			itt.next().accept(this);
		}
		addJoins();
	}

	/**
	 * Adds information to the query that is included for all queries,
	 * irrespective of search criteria.
	 */
	private void addFixedInformation() {
		selects.add("VOLUME_TB.NAME");
		selects.add("FILE_TB.NAME");
		selects.add("FILE_TB.SIZE");
		selects.add("FILE_TB.LASTMODIFIED");
		tables.add(VOLUME_TB);
		tables.add(FILE_TB);
		criteria.add("FILE_TB.VOLUMEFK = VOLUME_TB.ID");
	}

	/**
	 * Yields the prepared statement containing the SQL query.
	 * 
	 * @return PreparedStatement containing desired query.
	 * @throws SQLException
	 * 
	 */
	private PreparedStatement getPreparedStatement() throws SQLException {
		String preparedSql = createSql();
		PreparedStatement stmt = conn.prepareStatement(preparedSql);
		for (int i = 0; i < parameters.size(); i++) {
			Object parameter = parameters.get(i);
			stmt.setObject(i + 1, parameter);
		}
		return stmt;

	}

	@Override
	protected void buildQuery() throws SearchException {
		// TODO Auto-generated method stub

	}

	/**
	 * Creates the SQL text needed by the prepared statement.
	 * 
	 * @return String containing text for prepared statement.
	 */
	private String createSql() {
		StringBuffer buffer = new StringBuffer();
		buffer.append("SELECT ");
		addItems(buffer, selects.iterator(), ",");

		buffer.append("FROM ");
		addItems(buffer, tables.iterator(), ",");

		buffer.append("WHERE ");
		addItems(buffer, criteria.iterator(), " AND");
		return buffer.toString();
	}

	/**
	 * Helper method that adds items supplied by an iterator to a string buffer,
	 * separated by a given separator.
	 * 
	 * @param buffer
	 *            The buffer to which strings are to be appended.
	 * @param iter
	 *            The iterator providing items.
	 * @param separator
	 *            The string separating items.
	 */
	private void addItems(StringBuffer buffer, Iterator iter, String separator) {
		while (iter.hasNext()) {
			String select = (String) iter.next();
			buffer.append(select);
			if (iter.hasNext()) {
				buffer.append(separator);
			}
			buffer.append(" ");
		}
	}

	/**
	 * Adds a specific criterion with a given value if it does not already
	 * appear.
	 * 
	 * @param criterion
	 *            The column to be constrained.
	 * @param parameter
	 *            The value for the column.
	 */
	private void addCriterion(String criterion, String parameter) {
		if (!(criteria.contains(criterion) && parameters.contains(parameter))) {
			criteria.add(criterion);
			parameters.add(parameter);
		}
	}

	/**
	 * Adds any joins needed to support the submitted criteria.
	 * 
	 */
	private void addJoins() {
		if (tables.contains("KEYWORD_VOLUME_REL")) {
			// Must also contain KEYWORD_TB and VOLUME_TB
			criteria.add("KEYWORD_VOLUME_REL.KEYWORDFK = KEYWORD_TB.ID");
			criteria.add("KEYWORD_VOLUME_REL.VOLUMEFK = VOLUME_TB.ID");
		}
	}

	/**
	 * Visits a SearchVolumeName search criterion.
	 * 
	 * @param volumeName
	 *            The search criterion to visit.
	 * @see uk.me.mukherjee.archivedb.pd.search.SearchVisitor#visitVolumeName(uk.me.mukherjee.archivedb.pd.search.SearchVolumeName)
	 */
	public void visitVolumeName(SearchVolumeName volumeName) {
		addCriterion("VOLUME_TB.NAME LIKE ?", "%" + volumeName.getValue() + "%");
	}

	/**
	 * Visits a SearchFilename search criterion.
	 * 
	 * @param filename
	 *            The search criterion to visit.
	 * @see uk.me.mukherjee.archivedb.pd.search.SearchVisitor#visitFilename(uk.me.mukherjee.archivedb.pd.search.SearchFilename)
	 */
	public void visitFilename(SearchFilename filename) {
		addCriterion("FILE_TB.NAME LIKE ?", "%" + filename.getValue() + "%");
	}

	/**
	 * Visits a SearchStartFileDate search criterion.
	 * 
	 * @param date
	 *            The search criterion to visit.
	 * @see uk.me.mukherjee.archivedb.pd.search.SearchVisitor#visitStartFileDate(uk.me.mukherjee.archivedb.pd.search.SearchStartFileDate)
	 */
	public void visitStartFileDate(SearchStartFileDate date) {
		String dateString = getDateString(date.getYear(), date.getMonth(), date
				.getDay());
		addCriterion("TO_DAYS(FILE_TB.lastModified)>=TO_DAYS(?)", dateString);
	}

	/**
	 * Visits a SearchEndFileDate search criterion.
	 * 
	 * @param date
	 *            The search criterion to visit.
	 * @see uk.me.mukherjee.archivedb.pd.search.SearchVisitor#visitEndFileDate(uk.me.mukherjee.archivedb.pd.search.SearchEndFileDate)
	 */
	public void visitEndFileDate(SearchEndFileDate date) {
		String dateString = getDateString(date.getYear(), date.getMonth(), date
				.getDay());
		addCriterion("TO_DAYS(FILE_TB.lastModified)<=TO_DAYS(?)", dateString);
	}

	/**
	 * Visits a SearchKeyword search criterion.
	 * 
	 * @param keyword
	 *            The search criterion to visit.
	 * @see uk.me.mukherjee.archivedb.pd.search.SearchVisitor#visitKeyword(uk.me.mukherjee.archivedb.pd.search.SearchKeyword)
	 */
	public void visitKeyword(SearchKeyword keyword) {
		tables.add(KEYWORD_TB);
		tables.add(KEYWORD_VOLUME_REL);
		addCriterion("KEYWORD_TB.KEYWORD LIKE ?", keyword.getValue());
	}

	/**
	 * Helper method that creates a string representation of a year suitable for
	 * performing comparisons in SQL queries.
	 * 
	 * @param year
	 *            The year
	 * @param month
	 *            The month
	 * @param day
	 *            The day
	 * @return String representation of date
	 */
	private String getDateString(String year, String month, String day) {
		return "\"" + year + "-" + month + "-" + day + "\"";
	}

	/**
	 * Performs a search based on the provided search criteria and returns a
	 * SearchResult object.
	 * 
	 * @param searchCriteria
	 *            The list of ISearchCriterion objects.
	 * @return SearchResult containing files matching the provided criteria.
	 * @see uk.me.mukherjee.archivedb.pd.search.SearchVisitor#doSearch(java.util.List)
	 */
	public void doInternalSearch() throws SearchException {
		try {
			ResultSet rs = getPreparedStatement().executeQuery();
			VolumeSearchResult searchResult = new VolumeSearchResult();
			while (rs.next()) {
				String volumeName = rs.getString(1);
				String filename = rs.getString(2);
				int fileSize = rs.getInt(3);
				Date lastModified = rs.getDate(4);
				File file = new File();
				file.setName(filename);
				file.setSize(new Integer(fileSize));
				file.setLastModified(lastModified);
				file.setVolumeName(volumeName);
				searchResult.addFile(file);
			}
		} catch (SQLException e) {
			throw new SearchException(e);
		}
	}
}
